package org.kalai.unifypay.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.kalai.unifypay.bean.Bizcnf;
import org.kalai.unifypay.bean.Member;
import org.kalai.unifypay.bean.MemberBiz;
import org.kalai.unifypay.bean.Partner;
import org.kalai.unifypay.bean.PartnerBiz;
import org.kalai.unifypay.bean.Settlement;
import org.kalai.unifypay.bean.Transflow;

public class DataDao {
	int i = 0;
    private static final Logger logger = Logger.getLogger(DataDao.class);
    private QueryRunner queryRunner = null;

    private DataDao() {
        queryRunner = new QueryRunner(DBUtil.getDataSource());
    }

    public static DataDao getInstance() {
        return new DataDao();
    }

    //合作方相关操作
    public List<Partner> getPartners() {
        try {
            String sql = "SELECT * FROM partner WHERE status = 1";
            return queryRunner.query(sql, new BeanListHandler<>(Partner.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<PartnerBiz> getPartnerBizs() {
        try {
            String sql = "SELECT * FROM partner_biz WHERE status = 1";
            return queryRunner.query(sql, new BeanListHandler<>(PartnerBiz.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Partner getPartnerByCode(String partnerCode) {
        try {
            String sql = "SELECT * FROM partner WHERE partnerCode = ? and status = 1";
            return queryRunner.query(sql, new BeanHandler<>(Partner.class), partnerCode);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public PartnerBiz getPartnerBiz(String partnerCode, String bizcode) {
        try {
            String sql = "SELECT * FROM partner_biz WHERE partnerCode = ? and bizcode = ? and status = 1";
            return queryRunner.query(sql, new BeanHandler<>(PartnerBiz.class), partnerCode, bizcode);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
	
	public boolean updateMemberBizOFmemberCode(String memberNo,String merchantCode,String bizcode) throws SQLException {
	   String sql = "UPDATE member_biz SET memberCode=? WHERE memberNo=? and bizcode in (2001,2002,2003,2004)";
	   if(bizcode.equals("2"))
	      sql="UPDATE member_biz SET memberCode=? WHERE memberNo=? and bizcode in (1007,1008,1009,1010)";
       return queryRunner.update(sql,merchantCode,memberNo) > 0;
   }
    //会员相关操作
    public Member getMember(String memberNo) throws SQLException {
        String sql = "SELECT * FROM member WHERE memberNo = ?";
        return queryRunner.query(sql, new BeanHandler<>(Member.class), memberNo);
    }
    
    
    /**
     * 商户查询
    * @param partnerCode 合作商编码
    * @param memberNo 会员号
    * @return
    * @throws SQLException
    */
   public Member getMember(String partnerCode,String memberNo) throws SQLException {
        String sql = "SELECT * FROM member WHERE memberNo = ? and partnerCode=?";
        return queryRunner.query(sql, new BeanHandler<>(Member.class), memberNo,partnerCode);
    }
    public boolean addMember(Connection connection, Member member) throws SQLException {
        QueryRunner runner = new QueryRunner();
        String sql = "INSERT INTO member(memberNo, partnerCode, memberName, memberMobile, memberCertno, acctNo, bankNo, bankName, ip, gps) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return runner.update(
                connection, sql,
                member.getMemberNo(), member.getPartnerCode(), member.getMemberName(), member.getMemberMobile(), member.getMemberCertno(),
                member.getAcctNo(), member.getBankNo(), member.getBankName(), member.getIp(), member.getGps()
        ) > 0;
    }
	//根据商户号修改真实商户状态
    public boolean updateMemberAlipayStatus(String merchantCode, int status) throws SQLException {
       int result = 0;
       Connection conn = null;
       try {
           conn = DBUtil.getConnection();
           conn.setAutoCommit(false);
           String sql = "update member_alipay set status=? where merchantCode=?";
           result+=queryRunner.update(conn, sql,status, merchantCode);
           sql = "update member_biz set memberCode='' where memberCode=?";
           result+=queryRunner.update(conn, sql, merchantCode);
           conn.commit();
       } catch (Exception e) {
           conn.rollback();
           logger.error(String.format("[%s]真实商户编码状态修改异常:%s", merchantCode,e));
       } finally {
           conn.setAutoCommit(true);
           if (conn != null) conn.close();
       }
       return result > 1;
    }

    public boolean modifyMember(String memberNo, String memberMobile, String acctNo, String bankNo, String bankName) throws SQLException {
        String sql = "UPDATE member SET memberMobile = ?, acctNo = ?, bankNo = ?, bankName = ? WHERE memberNo = ?";
        return queryRunner.update(sql, memberMobile, acctNo, bankNo, bankName, memberNo) > 0;
    }

    //会员业务配置操作
    public List<Map<String, Object>> getMerchantBiz(String memberNo) throws SQLException {
        try {
            String sql = "SELECT bizcode,rate,status FROM member_biz WHERE memberNo = ?";
            return queryRunner.query(sql, new MapListHandler(), memberNo);
        } catch (Exception ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public MemberBiz getMerchantCode(String memberNo, String bizcode) throws SQLException {
        String sql = "SELECT * FROM member_biz WHERE memberNo = ? and bizcode = ?";
        return queryRunner.query(sql, new BeanHandler<>(MemberBiz.class), memberNo, bizcode);
    }

    public boolean addMemberBiz(Connection conn, String memberNo, String bizcode, String rate, String memberKey, String memberCode) throws SQLException {
        String sql = "INSERT INTO member_biz(memberNo, bizcode, rate, memberKey, memberCode) VALUES(?, ?, ?, ?, ?)";
        QueryRunner runner = new QueryRunner();
        return runner.update(conn, sql, memberNo, bizcode, rate, memberKey, memberCode) > 0;
    }

    public boolean deleteMemberBiz(Connection conn, String memberNo) throws SQLException {
        String sql = "DELETE FROM member_biz WHERE memberNo = ?";
        QueryRunner runner = new QueryRunner();
        return runner.update(conn, sql, memberNo) > 0;
    }

    public boolean updateMemberBizOFmemberCode(String memberNo, String merchantCode) throws SQLException {
        String sql = "UPDATE member_biz SET memberCode=? WHERE memberNo=?";
        return queryRunner.update(sql, merchantCode, memberNo) > 0;
    }

    //民生真实商户注册
    public boolean addAlipayMerchant(String merchantId, String merchantName, String shortName,
                                     String merchantAddress, String servicePhone, String category, String type, String merchantCode, String isReal) throws SQLException {
        String sql = "INSERT INTO member_alipay(merchantId, merchantName, shortName, merchantAddress, servicePhone, category, type, merchantCode,isReal) VALUES(?, ?, ?, ?, ?, ?, ?, ?,?)";
        return queryRunner.update(sql, merchantId, merchantName, shortName, merchantAddress, servicePhone, category, type, merchantCode, isReal) > 0;
    }

    //随机获取一个支付宝银行商户号
    public String getAlipayMerchantCode(String partnerCode, int type) throws SQLException {
        String sql = "SELECT * FROM member_alipay WHERE merchantId LIKE ? AND isReal='0' AND status = 1 AND type = ? ORDER BY RAND() LIMIT 1";
        return queryRunner.query(sql, new ScalarHandler<String>("merchantCode"), partnerCode + "%", type);
    }

    //交易相关操作
    public boolean addMemberTransaction(String tranxSN, String orderNo, String subject, String bizcode, int bizPattern, String partnerCode, String memberNo,
                                        long transAmt, long transFee, String settDate, String payAccount, Object respCode, Object respInfo, String notifyUrl, int status) throws SQLException {
        String sql = "INSERT INTO transflow"
                + "(tranxSN, orderNo, subject, bizcode, bizPattern, partnerCode, memberNo, transAmt, transFee, transTime, settDate, payAccount, respCode, respInfo, notifyUrl, status) "
                + "VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        return queryRunner.update(sql,
                tranxSN, orderNo, subject, bizcode, bizPattern, partnerCode, memberNo, transAmt / 100.0, transFee / 100.0, new Date(), settDate, payAccount, respCode, respInfo, notifyUrl, status) > 0;
    }

    public boolean updateTransflow(String tranxSN, String payAccount, long payAmt, int status, Object respCode, Object respInfo) throws SQLException {
        String sql = "UPDATE transflow SET payAccount = ?, payAmt = ?, payTime = ?, respCode = ?, respInfo = ?, status = ? WHERE tranxSN = ?";
        return queryRunner.update(sql, payAccount, payAmt / 100.0, new Date(), respCode, respInfo, status, tranxSN) > 0;
    }

    public boolean updateTransflowStatus(String tranxSN, int status) throws SQLException {
        String sql = "UPDATE transflow SET status = ? WHERE tranxSN = ?";
        return queryRunner.update(sql, status, tranxSN) > 0;
    }

    public Transflow getTransflow(String tranxSN) throws SQLException {
        String sql = "SELECT * FROM transflow WHERE tranxSN = ?";
        return queryRunner.query(sql, new BeanHandler<>(Transflow.class), tranxSN);
    }

    public List<Transflow> getTransflowList(Date dateBefore40, Date dateBefore20) throws SQLException {
        String sql = "SELECT * FROM transflow WHERE status = 1 AND bizcode in('1007','1008','1009','1010','2001','2002','2003','2004') AND transTime BETWEEN ? and ?";
        return queryRunner.query(sql, new BeanListHandler<>(Transflow.class), dateBefore40, dateBefore20);
    }

    public Transflow getTransflowByOrderNo(String memberNo, String orderNo) throws SQLException {
        String sql = "SELECT * FROM transflow WHERE memberNo=? and orderNo = ? limit 1";
        return queryRunner.query(sql, new BeanHandler<>(Transflow.class), memberNo, orderNo);
    }

    /**
     * 获取代付
     *
     * @param settNO 结算流水
     * @return
     * @throws SQLException
     */
    public Settlement getSettlement(String settNO) throws SQLException {
        String sql = "SELECT * FROM settlement WHERE settNo = ?";
        return queryRunner.query(sql, new BeanHandler<>(Settlement.class), settNO);
    }

    /**
     * 获取大于5分钟的未结算流水(环商通)
     *
     * @return
     * @throws SQLException
     */
    public List<Settlement> getSettlementList() throws SQLException {
        String sql = "SELECT * FROM settlement WHERE settStatus = 4 AND " +
                "(bizcode BETWEEN 1001 AND 1004 or bizcode = 3001 or bizcode = 3002) AND settNotifyTime > '2016-08-31' AND TIMESTAMPDIFF(SECOND,settTime,current_timestamp) > 5 * 60";
        return queryRunner.query(sql, new BeanListHandler<>(Settlement.class));
    }

    public Transflow getTransflowByOrderNo(String orderNo) throws SQLException {
        String sql = "SELECT * FROM transflow WHERE orderNo = ?";
        return queryRunner.query(sql, new BeanHandler<>(Transflow.class), orderNo);
    }

    public boolean addSettlement(String tranxSN, String settNo, String bizcode, Member member, double settAmt, int settStatus) throws SQLException {
        int result = 0;
        Connection conn = null;
        try {
            conn = DBUtil.getConnection();
            conn.setAutoCommit(false);
            String sql = "INSERT INTO settlement(settNo, bizcode, memberNo, settAcctNo, settBankNo, settBankName, settAmt, settStatus) VALUES(?, ?, ?, ?, ?, ?, ?, ?)";
            result += queryRunner.update(conn, sql, settNo, bizcode, member.getMemberNo(), member.getAcctNo(), member.getBankNo(), member.getBankName(), settAmt, settStatus);
            sql = "INSERT INTO settlement_detail(tranxSN, settNo, bizcode, settAmt) VALUES(?, ?, ?, ?)";
            result += queryRunner.update(conn, sql, tranxSN, settNo, bizcode, settAmt);
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            logger.error(e);
        } finally {
            conn.setAutoCommit(true);
            if (conn != null) conn.close();
        }
        return result > 1;
    }

    /**
     * 更新结算状态
     * @param settNo
     * @param respCode
     * @param respInfo
     * @param settStatus
     * @throws SQLException
     */
    public boolean updateSettlement(String settNo, String respCode, String respInfo, int settStatus) throws SQLException {
        String sql = "UPDATE settlement SET respCode = ?, respInfo = ?, settStatus = ?, settNotifyTime = ? WHERE settNo = ?";
        return queryRunner.update(sql, respCode, respInfo, settStatus, new Date(), settNo) > 0;
    }

    public List<Settlement> getSettlement() {
        try {
            String sql = "SELECT * FROM settlement WHERE settStatus = 2";
            return queryRunner.query(sql, new BeanListHandler<>(Settlement.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public List<Bizcnf> getBizcnfs() {
        try {
            String sql = "SELECT * FROM bizcnf";
            return queryRunner.query(sql, new BeanListHandler<>(Bizcnf.class));
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public Bizcnf getBizcnfByCode(String bizcode) {
        try {
            String sql = "SELECT * FROM bizcnf WHERE bizcode = ?";
            return queryRunner.query(sql, new BeanHandler<>(Bizcnf.class), bizcode);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }

    public boolean getWorkDay(String dateStr) {
        try {
            String sql = "SELECT * FROM workday WHERE date = ?";
            return queryRunner.query(sql, new BeanListHandler<>(String.class), dateStr).size() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }

    public static void main(String[] args) throws SQLException {
        System.out.println(DataDao.getInstance().getAlipayMerchantCode("P0008", 1));
    }

    public Settlement getSettlementBySettNo(String settNo) {
        try {
            String sql = "SELECT * FROM settlement WHERE settNo = ?";
            return queryRunner.query(sql, new BeanHandler<>(Settlement.class), settNo);
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }
    
    /**
     * 获取银行简称代码
    * @param bankName 银行名称
    * @param cardType 银行类型
    * @return
    */
   public String getShortCodeByBankName(String bankName,String cardType) {
       try {
           String sql = "SELECT shortCode from cj_paybank WHERE  ? LIKE CONCAT('%',bankName,'%') and cardType=? limit 1";
           return queryRunner.query(sql, new ScalarHandler<String>(), bankName,cardType);
       } catch (SQLException e) {
          logger.error("获取银行简称代码异常"+e);
          return null;
       }
   }
    
    
}
